package com.demo.designmode.template.case1;

import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @author jiaxXM
 * @date 2024/9/3
 */
@Service
public class UserServiceImpl implements UserService{

    /**
     * 1.装载相应的数据库的JDBC驱动并进行初始化
     * 2.建立JDBC和数据库之间的Connection连接
     * 3.创建Statement或者PreparedStatement接口，执行SQL语句
     * 4.处理和显示结果
     * 5.释放资源
     */

    private static final String GET_USER_BY_NAME_SQL = "SELECT `id`,`name`,`age`,`job` FROM `tb_user` WHERE `name` = '%s'";

    @Override
    public User getUserByName(String name) throws Exception {
        User user = new User();

        try (Connection connection = ConnectFactory.getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(String.format(GET_USER_BY_NAME_SQL, name))
        ){

            while (resultSet.next()) {
                //获取id
                user.setId(resultSet.getInt("id"));
                //获取名称
                user.setName(resultSet.getString("name"));
                //获取年龄
                user.setAge(resultSet.getInt("age"));
                //获取工作
                user.setJob(resultSet.getString("job"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }


        return user;
    }

    private static final String GET_USER_BY_ID_SQL = "SELECT `id`,`name`,`age`,`job` FROM `tb_user` WHERE `id` = '%s'";
    @Override
    public User getUserById(Integer id) throws Exception {
        User user = new User();
        //获取数据库连接
        try (Connection connection = ConnectFactory.getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(String.format(GET_USER_BY_ID_SQL, id))
        ) {
            while (resultSet.next()) {
                //获取id
                user.setId(resultSet.getInt("id"));
                //获取名称
                user.setName(resultSet.getString("name"));
                //获取年龄
                user.setAge(resultSet.getInt("age"));
                //获取工作
                user.setJob(resultSet.getString("job"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return user;
    }
}
